package com.citytransport.prokopjuk.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.LinkedList;

import com.citytransport.prokopjuk.connectDB.ConnectionPool;
import com.citytransport.prokopjuk.route.Route;
import com.citytransport.prokopjuk.vehicleDTO.Bus;
import com.citytransport.prokopjuk.vehicleDTO.Tram;
import com.citytransport.prokopjuk.vehicleDTO.Trolleybus;
import com.citytransport.prokopjuk.vehicleDTO.Vehicle;
import org.apache.log4j.Logger;

public class DAORoute implements IDAORoute {
    static Logger logger = Logger.getLogger(DAORoute.class.getName());
    private ConnectionPool pool;
    private DAOTram daoTram;
    private DAOBus daoBus;
    private DAOTrolleybus daoTrolleybus;
    
    public DAORoute() {
	pool = ConnectionPool.getConnectionPool();
	daoTram = new DAOTram();
	daoBus = new DAOBus();
	daoTrolleybus = new DAOTrolleybus();
    }

    public void create(Route route) {
	Connection con = pool.getConnection();
	PreparedStatement prepStmt = null;
	try {
	    prepStmt = con.prepareStatement("insert into route values(?,?,?)");
	    prepStmt.setInt(1, route.getRouteID());
	    prepStmt.setInt(2, route.getIntervalMinute());
	    prepStmt.setInt(3, route.getPrice());
	    prepStmt.executeUpdate();
	} catch (SQLException e) {
	    logger.error(e);
	} finally {
	    pool.returnConnecction(con);
	}
    }

    public void delete(Route route) {
	Connection con = pool.getConnection();
	PreparedStatement prepStmt = null;

	try {
	    prepStmt = con
		    .prepareStatement("delete from route where routeID = ?");
	    prepStmt.setInt(1, route.getRouteID());
	    prepStmt.execute();

	} catch (SQLException e) {
	    logger.error(e);
	} finally {
	    pool.returnConnecction(con);
	}

    }

    public void deleteAllRoutes() {
	Connection con = pool.getConnection();
	try {
	    Statement stmt = con.createStatement();
	    stmt.executeUpdate("delete from route");
	} catch (SQLException e) {
	    logger.error(e);
	} finally {
	    pool.returnConnecction(con);
	}

    }

    // TODO this version return only very simple presintation of the route. Only
    // price, ID, interval, !with out vehicles and stationList
    public Route read(int routeID) {
	Route route = new Route();
	Connection con = pool.getConnection();
	PreparedStatement prepStmt = null;
	try {
	    prepStmt = con
		    .prepareStatement("select routeID, price, intervalMinute from route "
			    + "where routeID = ?");
	    prepStmt.setInt(1, routeID);
	    ResultSet resSet = prepStmt.executeQuery();
	    resSet.next();
	    route.setRouteID(resSet.getInt("routeID"));
	    route.setPrice(resSet.getInt("price"));
	    route.setIntervalMinute(resSet.getInt("intervalMinute"));

	} catch (SQLException e) {
	    logger.error(e);
	} finally {
	    pool.returnConnecction(con);
	}

	return route;
    }

    /*
     * Be careful : do not try set routeID that equals already stored ID. This
     * bag will be fixed in next version.
     */
    public void update(Route route, int routeID, int intervalMinute, int price) {
	Connection con = pool.getConnection();
	PreparedStatement prepStmt = null;
	try {
	    prepStmt = con
		    .prepareStatement("update route set routeID=? , intervalMinute=?, price=? "
			    + "where routeID = ?");
	    prepStmt.setInt(1, routeID);
	    prepStmt.setInt(2, intervalMinute);
	    prepStmt.setInt(3, price);
	    prepStmt.setInt(4, route.getRouteID());
	    prepStmt.executeUpdate();
	} catch (SQLException e) {
	    logger.error(e);
	} finally {
	    pool.returnConnecction(con);
	}
    }

    public ArrayList<Route> readAllRoutes() {
	ArrayList<Route> routeList = new ArrayList<Route>();
	Connection con = pool.getConnection();
	try {
	    Statement stmt = con.createStatement();
	    ResultSet resSet = stmt.executeQuery("select * from route");
	    while (resSet.next()) {
		Route tmp = new Route();
		tmp.setRouteID(resSet.getInt("routeID"));
		tmp.setIntervalMinute(resSet.getInt("intervalMinute"));
		tmp.setPrice(resSet.getInt("price"));
		routeList.add(tmp);
	    }
	} catch (SQLException e) {
	    logger.error(e);
	} finally {
	    pool.returnConnecction(con);
	}
	return routeList;
    }

    public ArrayList<Route> readCertainRoute(String type) {
	ArrayList<Route> routeList = new ArrayList<Route>();
	Connection con = pool.getConnection();
	Statement stmt = null;
	try {
	    stmt = con.createStatement();
	    String query = "select * from route where routeid in" +
	    		"(select routeID from vehicle where vehicle.vehicleid in " +
	    		"(select "+ type +"id  from " +type+"))";
	    ResultSet resSet = stmt.executeQuery(query);
	    while (resSet.next()) {
		Route tmp = new Route();
		tmp.setRouteID(resSet.getInt("routeID"));
		tmp.setIntervalMinute(resSet.getInt("intervalMinute"));
		tmp.setPrice(resSet.getInt("price"));
		routeList.add(tmp);
	    }
	} catch (SQLException e) {
	    logger.error(e);
	} finally {
	    pool.returnConnecction(con);
	}
	return routeList;
    }

    public ArrayList<? extends Vehicle> readVehicle(int routeID) {
	ArrayList<Tram> trams = new ArrayList<Tram>(daoTram.readAtRoute(routeID));
	if(!trams.isEmpty())
	    return trams;
	ArrayList<Bus> buses = new ArrayList<Bus>(daoBus.readAtRoute(routeID));
	if(!buses.isEmpty())
	    return buses;
	ArrayList<Trolleybus> troll = new ArrayList<Trolleybus>(daoTrolleybus.readAtRoute(routeID));
	if(!troll.isEmpty())
	    return troll;
	return null;
    }

//    @SuppressWarnings("unchecked")
//    public <T extends Vehicle> T readVehicle(int vehicleID) {
//	LinkedList<Integer> tramIndex = new LinkedList<Integer>(daoTram.getAllTramID());
//	if(tramIndex.contains(vehicleID)){
//	    return  (T) daoTram.read(vehicleID);
//	}
//	LinkedList<Integer> busIndex = new LinkedList<Integer>(daoBus.getAllBusID());
//	if(busIndex.contains(vehicleID)){
//	    return (T) daoBus.read(vehicleID);
//	}
//	LinkedList<Integer> trolleybusIndex = new LinkedList<Integer>(daoTrolleybus.getAllTrolleybusID());
//	if(trolleybusIndex.contains(vehicleID)){
//	    return (T) daoTrolleybus.read(vehicleID);
//	}
//	return null;
//    }

    

}
